MySQL のサブクエリって、ほんとに遅いの？
こんにちは、みかみです。
今日のクラスメソッドのAWSおみくじ、RDS（t1.micro）でしたー！（昨日は Aurora！＠＠ｖ
はじめに
前職＠アプリ開発時、MySQL のクエリチューニングをさせていただく機会がありました。
ユーザー入力のキーワードで全文検索 → 見つかったレコードを返すのですが、所要時間、約3分。。
Apache タイムアウトして画面真っ白。。。（泣きそうでした＞＜。
EXPLAINで実行計画を調べた際に、select_typeにはクエリの種類が表示されるのだが、代表的なサブクエリには次の3つのパターンがある。
- SUBQUERY
- DEPENDENT SUBQUERY
- DERIVED
結論から言おう。遅いのは2番目、DEPENDENT SUBQUERYである。DEPENDENT SUBQUERYとはいわゆる相関サブクエリに相当するもので、サブクエリにおいて外部クエリのカラムを参照しているサブクエリのことである。そして、MySQLのオプティマイザはたびたび相関関係のないもの、つまり本来はSUBQUERYと分類されるべきものをDEPENDENT SUBQUERYであると判断してしまう。そのため、多くの場合においてサブクエリが遅くなってしまうのである。
DEPENDENT SUBQUERY ＝ トラウマ。。。（こちらのブログ、当時、私のバイブルでしたｖ
そして、現在のデータ分析系のおしごとさせていただくようになってから読んだ本のコラムで
「サブクエリが遅いのは MySQL だけ！」
と、明言されているのを見ました。。。（その本は、PostgreSQL ベースで書かれてました。
やりたいこと
- MySQL のサブクエリはほんとに遅いのか確かめてみたい。
確認環境
- OS：Windows10（Mac VMware Fusion）
- MySQL（local）：5.6.26 / 5.5.39
- PostgreSQL（local）：9.6.1
やってみた
データと確認用クエリを準備
Sakila DB
MySQL のサンプル DB、Sakila Sample Database を使います。
MySQL ロゴのイルカさん、お名前を Sakila さんとおっしゃるそうで（なので sakila-db なのですねv
いただいてきた sakila-schema.sql と sakila-data.sql を実行すると、sakila DB とテーブル一式、サンプルデータがロードできました。
mysql> select table_name, table_rows from information_schema.TABLES where table_schema = 'sakila'; +----------------------------+------------+ | table_name | table_rows | +----------------------------+------------+ | actor | 200 | | actor_info | NULL | | address | 603 | | category | 16 | | city | 600 | | country | 109 | | customer | 599 | | customer_list | NULL | | film | 1000 | | film_actor | 5462 | | film_category | 1000 | | film_list | NULL | | film_text | 1000 | | inventory | 4581 | | language | 6 | | nicer_but_slower_film_list | NULL | | payment | 16086 | | rental | 16005 | | sales_by_film_category | NULL | | sales_by_store | NULL | | staff | 2 | | staff_list | NULL | | store | 2 | +----------------------------+------------+ 23 rows in set (0.01 sec)
この Skila DB、国→映画→レンタルビデオ（DVD？ のサンプルデータが入ってます。
今回は、顧客マスタ（customer）と支払い情報テーブル（payment）を使い、サブクエリを使った SQL を投げてみます。
オリジナルの顧客マスタはレコード数が少ないので、実行時間をわかりやすくするために、レコードを増やします。
customer と同じ定義の customer_2 テーブルを create して、customer_id カラムの型を BIGINT に変更（smallint だとオーバーフローしちゃうので。
mysql> create table customer_2 like customer; Query OK, 0 rows affected (0.01 sec) mysql> alter table customer_2 modify customer_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT; Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0
customer から select したレコードを customer_2 に insert したら、ID だけインクリメント（AUTO_INCREMENT）してレコード数を増やします。
mysql> insert into customer_2 (select * from customer); Query OK, 599 rows affected (0.00 sec) Records: 599 Duplicates: 0 Warnings: 0 mysql> insert into customer_2 (store_id, first_name, last_name, email, address_id, active, create_date, last_update) (select store_id, first_name, last_name, email, address_id, active, create_date, last_update from customer_2); Query OK, 599 rows affected (0.01 sec) Records: 599 Duplicates: 0 Warnings: 0
何度か insert して、120万件ほどに増やしました。
mysql> (select 'customer_2' as 'table', (select count(*) from customer_2) as records) union (select 'payment' as 'table', (select count(*) from payment) as records); +------------+---------+ | table | records | +------------+---------+ | customer_2 | 1226752 | | payment | 16049 | +------------+---------+ 2 rows in set (0.39 sec)
テーブルには、こんなデータが入っています。
mysql> select * from customer_2 limit 3; +-------------+----------+------------+-----------+-------------------------------------+------------+--------+---------------------+---------------------+ | customer_id | store_id | first_name | last_name | email | address_id | active | create_date | last_update | +-------------+----------+------------+-----------+-------------------------------------+------------+--------+---------------------+---------------------+ | 1 | 1 | MARY | SMITH | MARY.SMITH@sakilacustomer.org | 5 | 1 | 2006-02-14 22:04:36 | 2006-02-15 04:57:20 | | 2 | 1 | PATRICIA | JOHNSON | PATRICIA.JOHNSON@sakilacustomer.org | 6 | 1 | 2006-02-14 22:04:36 | 2006-02-15 04:57:20 | | 3 | 1 | LINDA | WILLIAMS | LINDA.WILLIAMS@sakilacustomer.org | 7 | 1 | 2006-02-14 22:04:36 | 2006-02-15 04:57:20 | +-------------+----------+------------+-----------+-------------------------------------+------------+--------+---------------------+---------------------+ 3 rows in set (0.00 sec) mysql> select * from payment limit 3; +------------+-------------+----------+-----------+--------+---------------------+---------------------+ | payment_id | customer_id | staff_id | rental_id | amount | payment_date | last_update | +------------+-------------+----------+-----------+--------+---------------------+---------------------+ | 1 | 1 | 1 | 76 | 2.99 | 2005-05-25 11:30:37 | 2006-02-15 22:12:30 | | 2 | 1 | 1 | 573 | 0.99 | 2005-05-28 10:35:23 | 2006-02-15 22:12:30 | | 3 | 1 | 1 | 1185 | 5.99 | 2005-06-15 00:54:12 | 2006-02-15 22:12:30 | +------------+-------------+----------+-----------+--------+---------------------+---------------------+ 3 rows in set (0.00 sec)
確認用クエリ
準備した確認用クエリはこちら。
総額 $100 超ご利用いただいた顧客数をカウントします。
select
count(*)
from
customer_2
where
customer_id in(
select
s.customer_id
from (
select
customer_id,
sum(amount) as total
from
payment
group by customer_id) s
where
s.total > 100
);
payment から 顧客ごとの支払い総額を出して、総額 $100 以上の顧客ID を抽出します。
payment から select した customer_id を、一つ一つ customer_2 の customer_id と比較する相関サブクエリとして処理される、はず。
MySQL 5.6 で確認
MySQL の Version は、5.6.26（XAMPP 5.6.12 でインストール）です。
確認用クエリを実行します。
mysql> select count(*) from customer_2 where customer_id in(select s.customer_id from (select customer_id, sum(amount) as total from payment group by customer_id) s where s.total > 100); +----------+ | count(*) | +----------+ | 395 | +----------+ 1 row in set (0.03 sec)
実行時間、30ms ？！
あれ？ 速い！（もっと遅いと思ってたのに。。＠＠？！
実行計画確認します。
mysql> explain select count(*) from customer_2 where customer_id in(select s.customer_id from (select customer_id, sum(amount) as total from payment group by customer_id) s where s.total > 100); +----+--------------+-------------+--------+--------------------+--------------------+---------+-------------------------+-------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------+-------------+--------+--------------------+--------------------+---------+-------------------------+-------+--------------------------+ | 1 | PRIMARY | <subquery2> | ALL | NULL | NULL | NULL | NULL | NULL | NULL | | 1 | PRIMARY | customer_2 | eq_ref | PRIMARY | PRIMARY | 8 | <subquery2>.customer_id | 1 | Using where; Using index | | 2 | MATERIALIZED | <derived3> | ALL | NULL | NULL | NULL | NULL | 16086 | Using where | | 3 | DERIVED | payment | index | idx_fk_customer_id | idx_fk_customer_id | 2 | NULL | 16086 | NULL | +----+--------------+-------------+--------+--------------------+--------------------+---------+-------------------------+-------+--------------------------+ 4 rows in set (0.00 sec)
なんと。。
select_type に、相関サブクエリを表す DEPENDENT SUBQUERY （トラウマ）が出てきません。。
そして、MATERIALIZED って、なんですか？（あせ
eq_refって、JOIN するときの type では？（ちゃんとプライマリーキーで JOIN してる？！
調べてみたら、MySQL のオプティマイザ、進化してたのですね！＠＠ｖ
- MySQL 5.6 リファレンスマニュアル 8.8.2 EXPLAIN 出力フォーマット
- MySQL 5.6 リファレンスマニュアル 8.2.1.18.2 サブクエリー実体化によるサブクエリーの最適化
5.6 ならサブクエリ、そんなに遅くなくなったんだ！
どのくらい速くなったのかしら・・・？（わくわく
MySQL 5.5 で確認
MySQL 5.5.39 を XAMPP（1.8.2）でインストールして、先ほどと同じように Sakila DB を準備しました。
C:\Users\mikami.yuki>mysql -u root -ppassword Warning: Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.5.39 MySQL Community Server (GPL)
mysql> (select 'customer_2' as 'table', (select count(*) from customer_2) as records) union (select 'payment' as 'table', (select count(*) from payment) as records); +------------+---------+ | table | records | +------------+---------+ | customer_2 | 1226752 | | payment | 16049 | +------------+---------+ 2 rows in set (0.39 sec)
実行してみると・・・
mysql> select count(*) from customer_2 where customer_id in(select s.customer_id from (select customer_id, sum(amount) as total from payment group by customer_id) s where s.total > 100); +----------+ | count(*) | +----------+ | 395 | +----------+ 1 row in set (1 min 21.34 sec)
実行時間、約 1分20秒。。（ですよねー。。。
実行計画取ってみます。
mysql> explain select count(*) from customer_2 where customer_id in(select s.customer_id from (select customer_id, sum(amount) as total from payment group by customer_id) s where s.total > 100); +----+--------------------+------------+-------+---------------+--------------------+---------+------+---------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+------------+-------+---------------+--------------------+---------+------+---------+--------------------------+ | 1 | PRIMARY | customer_2 | index | NULL | idx_fk_store_id | 1 | NULL | 1226888 | Using where; Using index | | 2 | DEPENDENT SUBQUERY | <derived3> | ALL | NULL | NULL | NULL | NULL | 599 | Using where | | 3 | DERIVED | payment | index | NULL | idx_fk_customer_id | 2 | NULL | 16451 | | +----+--------------------+------------+-------+---------------+--------------------+---------+------+---------+--------------------------+ 3 rows in set (0.05 sec)
でました、DEPENDENT SUBQUERY。。（あわあわ・・
customer_2 の 120万件に対して、フルインデックススキャンかかってます。。
key にも関係ないカラム（store_id）が選択されてたり。。。
ちなみに、実行するクエリを、IN 句ではなく JOIN を使うように変えてみると・・・
mysql> select count(*) from customer_2 c inner join (select s.customer_id from (select customer_id, sum(amount) as total from payment group by customer_id) s where s.total > 100) p on c.customer_id = p.customer_id; +----------+ | count(*) | +----------+ | 395 | +----------+ 1 row in set (0.01 sec)
実行時間、約 10ミリ秒（ですよねー。。。
当然、実行計画も変わりました。
mysql> explain select count(*) from customer_2 c inner join (select s.customer_id from (select customer_id, sum(amount) as total from payment group by customer_id) s where s.total > 100) p on c.customer_id = p.customer_id; +----+-------------+------------+--------+---------------+--------------------+---------+---------------+-------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+---------------+--------------------+---------+---------------+-------+--------------------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 395 | | | 1 | PRIMARY | c | eq_ref | PRIMARY | PRIMARY | 8 | p.customer_id | 1 | Using where; Using index | | 2 | DERIVED | <derived3> | ALL | NULL | NULL | NULL | NULL | 599 | Using where | | 3 | DERIVED | payment | index | NULL | idx_fk_customer_id | 2 | NULL | 15422 | | +----+-------------+------------+--------+---------------+--------------------+---------+---------------+-------+--------------------------+ 4 rows in set (0.03 sec)
対象レコード数の差こそあるものの、これって、MySQL 5.6 の IN 句クエリの実行計画とほぼ同じ？！（やっぱり 5.6 ではオプティマイザが JOIN してくれてる＠＠！
PostgreSQL で確認
同じクエリを、PostgreSQL（9.6.1）でも確認してみます。
Sakila DB の DDL はそのまま使えないので、必要なテーブルだけ修正してデータを insert しました。
postgres=# (select 'customer_2' as table, (select count(*) from customer_2) as records) union (select 'payment' as table, (select count(*) from payment) as records); table | records ------------+--------- customer_2 | 1226752 payment | 16049 (2 行) postgres=# \timing タイミングは on です。 postgres=# select count(*) from customer_2 where customer_id in(select s.customer_id from (select customer_id, sum(amount) as total from payment group by customer_id) s where s.total > 100); count ------- 395 (1 行) 時間: 12.069 ms
さすがポスグレ、 12 ms！
実行計画見てみると・・・
postgres=# explain select count(*) from customer_2 where customer_id in(select s.customer_id from (select customer_id, sum(amount) as total from payment group by customer_id) s where s.total > 100);
QUERY PLAN
---------------------------------------------------------------------------------------------------
Aggregate (cost=6914.32..6914.33 rows=1 width=8)
-> Nested Loop (cost=375.16..5380.88 rows=613376 width=0)
-> HashAggregate (cost=374.74..382.22 rows=599 width=34)
Group Key: payment.customer_id
Filter: (sum(payment.amount) > '100'::numeric)
-> Seq Scan on payment (cost=0.00..294.49 rows=16049 width=8)
-> Index Only Scan using customer_2_pkey on customer_2 (cost=0.43..8.32 rows=1 width=8)
Index Cond: (customer_id = payment.customer_id)
(8 行)
payment テーブルにフルスキャン（ Seq Scan ）かけてはいるものの、ちゃんと Nested Loop で結合使ってくれてます。
実行時間比較
下表、120万件レコードに IN 句のクエリを投げた実行時間の比較です。
※コンソールからDB接続 → クエリを5回実行して、コンソールに表示された実行時間の平均値を出しました。
| 89.346 sec | 0.08 sec | 8.861 ms |
※注：MySQL は秒単位、PostgreSQLはミリ秒単位です。。
やっぱ、PostgreSQL の方が速いですね。。（5.6 と比較しても、約10倍。。
でも MySQL も、5.6 以降ならば、そんなに遅くないんでない？！＠＠ｖ
おわりに（まとめ・所感）
- MySQL 5.5 まで、サブクエリはやっぱり遅い。。
- MySQL 5.6 からはそんなに遅くなくなった！
- MySQL とはいっても、バージョンによって全然違う！
- オプティマイザは進化してる！
- DBのアーキテクチャって奥が深いｖ
